package com.cn.yang.dao.impl;

import com.cn.yang.dao.ProductDao;
import com.cn.yang.model.production.ProductClassifyModel;
import com.cn.yang.model.production.ProductModel;
import com.cn.yang.util.JDBC;
import com.cn.yang.util.PageUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    JDBC jdbc = new JDBC();
//查询分类
    @Override
    public List<ProductClassifyModel> findClassify() {
        String sql = "select * from productclassify";
        ResultSet resultSet = jdbc.getResultSet(sql);
        List<ProductClassifyModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ProductClassifyModel model = new ProductClassifyModel();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return list;
    }
//添加产品
    @Override
    public int addProduct(ProductModel model) {
        String sql = "insert into product(`name`,`img`,`content`,`diliver`,`averageprice`,`price`,`quality`,`payment`,`information`,`classify`)" +
                " values ('"+model.getName()+"','"+model.getImg()+"','"+model.getContent()+"'" +
                ",'"+model.getDiliver()+"','"+model.getAverageprice()+"','"+model.getPrice()+"'"+
                ",'"+model.getQuality()+"','"+model.getPayment()+"','"+model.getInformation()+"'"+
                ",'"+model.getClassify()+"')";
        int result = jdbc.update(sql);
        jdbc.closeConn();
        return result;
    }
//查询产品
    @Override
    public List<ProductModel> findProduct(ProductModel productModel) {
        String sql = "SELECT a.*,b.`name` as classifyname FROM product a LEFT JOIN productclassify b ON a.classify =b.id ";
        if (!"".equals(productModel.getSortUserUtil().getSortClassifyName())){
            sql +=" where a.classify = "+productModel.getSortUserUtil().getSortClassifyName()+"";
        }
        if ((!"".equals(productModel.getSortUserUtil().getSortId()))||(!"".equals(productModel.getSortUserUtil().getSortAverageprice()))||(!"".equals(productModel.getSortUserUtil().getSortPrice()))){
            sql +=" order by ";
            if (!"".equals(productModel.getSortUserUtil().getSortId())){
                sql +=" id "+productModel.getSortUserUtil().getSortId()+"";
            }
            if (!"".equals(productModel.getSortUserUtil().getSortAverageprice())){
                sql +=" averageprice "+productModel.getSortUserUtil().getSortAverageprice()+"";
            }
            if (!"".equals(productModel.getSortUserUtil().getSortPrice())){
                sql +=" price "+productModel.getSortUserUtil().getSortPrice()+"";
            }
        }
       
        sql+=" limit "+productModel.getPageUtil().getPageNumber()+","+productModel.getPageUtil().getPageSize()+"";
        ResultSet resultSet = jdbc.getResultSet(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()){
                ProductModel model = new ProductModel();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setImg(resultSet.getString("img"));
                model.setDiliver(resultSet.getString("diliver"));
                model.setAverageprice(resultSet.getString("averageprice"));
                model.setPrice(resultSet.getString("price"));
                model.setQuality(resultSet.getString("quality"));
                model.setPayment(resultSet.getString("payment"));
                model.setInformation(resultSet.getString("information"));
                model.setClassifyName(resultSet.getString("classifyname"));
                model.setContent(resultSet.getString("content"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public List<ProductModel> findProduct() {
        String sql = "SELECT a.*,b.`name` as classifyname FROM product a LEFT JOIN productclassify b ON a.classify =b.id ";
        ResultSet resultSet = jdbc.getResultSet(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()){
                ProductModel model = new ProductModel();
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setImg(resultSet.getString("img"));
                model.setDiliver(resultSet.getString("diliver"));
                model.setAverageprice(resultSet.getString("averageprice"));
                model.setPrice(resultSet.getString("price"));
                model.setQuality(resultSet.getString("quality"));
                model.setPayment(resultSet.getString("payment"));
                model.setInformation(resultSet.getString("information"));
                model.setClassifyName(resultSet.getString("classifyname"));
                model.setContent(resultSet.getString("content"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
//查询总条数
    @Override
    public int countProduct() {
        String sql = "SELECT COUNT(*) as count FROM product";
        ResultSet resultSet = jdbc.getResultSet(sql);
        int a = 0;
        try{
            ProductModel model = new ProductModel();
            while (resultSet.next()){
                model.setCount(resultSet.getInt("count"));
                a = model.getCount();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.closeConn();
        return a;
    }
//通过Id删除一条数据
    @Override
    public int deleteById(Integer id) {
        String sql = "delete from `product` where id ="+id;
        int result = jdbc.update(sql);
        if (result==1){
            jdbc.closeConn();
            return 1;
        }else {
            jdbc.closeConn();
            return 0;
        }
    }

    @Override
    public ProductModel selectById(Integer id) {
        String sql = "SELECT a.*,b.`name` as classifyname FROM product a LEFT JOIN productclassify b ON a.classify =b.id where a.id = " +id ;
        ResultSet resultSet = jdbc.getResultSet(sql);
        ProductModel model = new ProductModel();
        try {
            while (resultSet.next()){
                model.setId(resultSet.getInt("id"));
                model.setName(resultSet.getString("name"));
                model.setImg(resultSet.getString("img"));
                model.setDiliver(resultSet.getString("diliver"));
                model.setAverageprice(resultSet.getString("averageprice"));
                model.setPrice(resultSet.getString("price"));
                model.setQuality(resultSet.getString("quality"));
                model.setPayment(resultSet.getString("payment"));
                model.setInformation(resultSet.getString("information"));
                model.setClassifyName(resultSet.getString("classifyname"));
                model.setClassify(resultSet.getInt("classify"));
                model.setContent(resultSet.getString("content"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int updateProduct(ProductModel model) {
        String sql = "update product set" +
                " `name`='"+model.getName()+"',`img`='"+model.getImg()+"',`content`='"+model.getContent()+"'" +
                ",`diliver`='"+model.getDiliver()+"',`averageprice`='"+model.getAverageprice()+"',`price`='"+model.getPrice()+"'"+
                ",`quality`='"+model.getQuality()+"',`payment`='"+model.getPayment()+"',`information`='"+model.getInformation()+"'"+
                ",`classify`='"+model.getClassify()+"' where id = "+ model.getId();
        int result = jdbc.update(sql);
        jdbc.closeConn();
        return result;
    }
}
